Spring笔记（十三）—— Spring JDBC 访问数据库之 JdbcTemplate 及错误处理

Spring JDBC 是 Spring 所提供的持久层技术，它主要目的是降低使用 JDBC API 的门槛，以一种更直接、更简洁的方式使用 JDBC API。在 Spring JDBC 里，只需要完成定义连接数据库的参数、指定 SQL 语句、声明参数及参数值这些必要的事，而将资源获取、Statement 创建、资源释放以及异常处理等繁杂而乏味的工作交给 Spring JDBC。

JdbcTemplate

JdbcTemplate 是 JDBC 核心包中的主要类，它可以处理资源的创建和释放，帮助我们避免忘记关闭连接等类似错误。JdbcTemplate 类执行 SQL 查询，更新语句和存储过程调用，对 ResultSet 执行迭代和提取返回的参数值。它还能捕获 JDBC 异常，并将它们转换为 org.springframework.dao 包中定义的通用的、更丰富的异常层次结构。

Querying (SELECT)

下面是一个获取关系中行数的简单查询：

int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

使用绑定变量的简单查询：

int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
            "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

查询得到一个 String 变量：

String lastName = this.jdbcTemplate.queryForObject("select last_name from t_actor where id = ?",
            new Object[]{1212L}, String.class);

查询和填充单个域对象：

Actor actor = this.jdbcTemplate.queryForObject(
        "select first_name, last_name from t_actor where id = ?",
        new Object[]{1212L},
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

查询和填充多个域对象：

List<Actor> actors = this.jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        new RowMapper<Actor>() {
            public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
                Actor actor = new Actor();
                actor.setFirstName(rs.getString("first_name"));
                actor.setLastName(rs.getString("last_name"));
                return actor;
            }
        });

Updating (INSERT/UPDATE/DELETE)

使用 update(..) 方法执行插入、更新和删除操作，参数值通常由 var 参数或对象数组提供。

this.jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)",
        "Leonor", "Watling");

this.jdbcTemplate.update(
        "update t_actor set last_name = ? where id = ?",
        "Banjo", 5276L);

this.jdbcTemplate.update(
        "delete from actor where id = ?",
        Long.valueOf(actorId));

Execute

我们可以使用 execute（..） 方法执行任意的 SQL 语句，因此该方法通常用于 DDL（数据库定义语言）。

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

示例

JdbcTemplate 类的实例为线程安全的，这意味着我们可以配置 JdbcTemplate 的单实例，并将此共享引用安全地注入到多个 DAO。使用 JdbcTemplate 类（以及相关的 NamedParameterJdbcTemplate 类）的一个常见做法是在 Spring 配置文件中配置一个 DataSource，然后将共享的 DataSource bean 依赖注入到 DAO 类中; JdbcTemplate 在 DataSource 的 setter 方法中创建。

DAO 部分如下示例：

public class JdbcCorporateEventDao implements CorporateEventDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

相关的 XML 配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>
</beans>

上述的配置方法也可以使用显示配置的方法替代，显式配置的替代方法是对依赖注入使用组件扫描和注释支持 在这种情况下，使用 @Repository 注释类（这使它成为组件扫描的候选类），并使用 @Autowired 注释 DataSource 的 setter 方法。

DAO 部分如下示例：

@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

相关的 XML 配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- Scans within the base package of the application for
          @Component classes to configure as beans -->
    <context:component-scan base-package="org.springframework.docs.test" />

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <context:property-placeholder location="jdbc.properties"/>
</beans>

NamedParameterJdbcTemplate

NamedParameterJdbcTemplate 类增加了使用命名参数对 JDBC 语句进行编程的支持，而不是仅使用占位符（’？’）参数来对 JDBC 语句进行编程。NamedParameterJdbcTemplate 类封装了一个 JdbcTemplate，并委托给包装的 JdbcTemplate 来完成它的大部分工作。
MapSqlParameterSource 实现类内部通过一个 Map 存储参数，可以通过 addValue(String paramName, Object value) 或 addValue(Map values) 添加参数，并通过参数名和 SQL 语句的命名参数匹配的方式绑定参数。

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActorsByFirstName(String firstName) {
    String sql = "select count(*) from T_ACTOR where first_name = :first_name";
    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
    // 还可以使用基于 Map 的格式，MapSqlParameterSource 只是对 Map 的简单实现
    // Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName);
    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

另一个 SqlParameterSource 实现类是 BeanPropertySqlParameterSource，这个类将一个 JavaBean 对象封装成一个参数源，以便通过 JavaBean 属性名和 SQL 语句中命名参数匹配的方式绑定参数。

public class Actor {
    private Long id;
    private String firstName;
    private String lastName;

    public String getFirstName() {
        return this.firstName;
    }
    public String getLastName() {
        return this.lastName;
    }
    public Long getId() {
        return this.id;
    }
    // setters omitted...
}

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActors(Actor exampleActor) {
    // notice how the named parameters match the properties of the above 'Actor' class
    String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

SQLExceptionTranslator

SQLExceptionTranslator 是一个接口类，可以在 SQLExceptions 和 Spring 的 org.springframework.dao.DataAccessException 之间进行转换。SQLErrorCodeSQLExceptionTranslator 是 SQLExceptionTranslator 的默认实现，此实现使用特定的供应商代码，它比 SQLState 实现更精确，此类由 SQLErrorCodesFactory 创建和填充，SQLErrorCodesFactory 是创建 SQLErrorCodes 的工厂类，且是基于名为 sql-error-codes.xml 的配置文件的内容创建的。

自定义 SQLErrorCodeSQLExceptionTranslator 类：

public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
    protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
        if (sqlex.getErrorCode() == -12345) {
            return new DeadlockLoserDataAccessException(task, sqlex);
        }
        return null;
    }
}

在此示例中，特定错误代码 -12345 被转换，其他错误由默认转换器实现进行转换。要使用此自定义转换器，需要通过方法 setExceptionTranslator 将其传递给 JdbcTemplate，并将此 JdbcTemplate 用于需要此转换器的所有数据访问处理中。下面是如何使用自定义转换器的示例：

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
    // create a JdbcTemplate and set data source
    this.jdbcTemplate = new JdbcTemplate();
    this.jdbcTemplate.setDataSource(dataSource);

    // create a custom translator and set the DataSource for the default translation lookup
    CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
    tr.setDataSource(dataSource);
    this.jdbcTemplate.setExceptionTranslator(tr);
}

public void updateShippingCharge(long orderId, long pct) {
    // use the prepared JdbcTemplate for this update
    this.jdbcTemplate.update("update orders set shipping_charge = shipping_charge * ? / 100"
                + " where id = ?", pct, orderId);
}

更新数据库

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class ExecuteAnUpdate {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void setName(int id, String name) {
        this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id);
    }
}

返回自增键

update() 方法支持检索由数据库生成的主键，该方法将 PreparedStatementCreator 作为其第一个参数，是指定所需的 insert 语句的方式；另一个参数是　KeyHolder，它包含更新成功返回时生成的密钥。

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);

// keyHolder.getKey() now contains the generated key

批量操作

如果需要一次性插入或更新多条记录，可以简单地通过多次调用 update() 方法完成任务，也可以使用 JDBCTemplate 批量数据更改的方法。一般后者的性能更好，因为更新的数据将被批量发送到数据库中，它减少了对数据库访问的次数。

使用 JdbcTemplate 进行批量操作

使用 JdbcTemplate 批量操作需要实现指定接口 BatchPreparedStatementSetter 的两个方法，并将其实现作为 batchUpdate 方法的第二个参数，此方法的调用次数为 getBatchSize 方法中返回的次数。使用 getBatchSize 方法返回当前批处理的大小；使用 setValues 方法设置预准备语句的参数值。以下示例根据列表中的条目更新 actor 表，整个列表在本示例中用作批处理：

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        int[] updateCounts = jdbcTemplate.batchUpdate(
            "update t_actor set first_name = ?, last_name = ? where id = ?",
            new BatchPreparedStatementSetter() {
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    ps.setString(1, actors.get(i).getFirstName());
                    ps.setString(2, actors.get(i).getLastName());
                    ps.setLong(3, actors.get(i).getId().longValue());
                }

                public int getBatchSize() {
                    return actors.size();
                }
            });
        return updateCounts;
    }

    // ... additional methods
}

使用 List 进行批量操作

JdbcTemplate 和 NamedParameterJdbcTemplate 都提供了批量更新的替代方法，不是实现特殊的批处理接口，而是将调用中的所有参数值作为 List 提供。namedParameterJdbcTemplate 提供了一个 SqlParameterSource 数组，批处理的每个成员都是一个条目，使用 SqlParameterSource.createBatch 方法可以创建此数组，其传入的参数为包含参数值的 JavaBeans 数组或 Maps 数组。

使用 NamedParameterJdbcTemplate 的批量更新：

public class JdbcActorDao implements ActorDao {
    private NamedParameterTemplate namedParameterJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
                  "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                  batch);
        return updateCounts;
    }

    // ... additional methods
}

使用 JdbcTemplate 和占位符 “？” 的批量更新：

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[] {
                      actor.getFirstName(),
                      actor.getLastName(),
                      actor.getId() };
            batch.add(values);
        }
        int[] updateCounts = jdbcTemplate.batchUpdate(
                    "update t_actor set first_name = ?, last_name = ? where id = ?",
                    batch);
        return updateCounts;
    }

    // ... additional methods
}

多批次的批量操作

batchUpdate 方法的参数除了 SQL 语句之外，还有一个包含参数的对象集合和每个批次所需更新的数量，以及 ParameterizedPreparedStatementSetter（用于设置预准备语句的参数值）。框架在所提供的值上循环，并将更新调用分成指定大小的批处理。

如下是每批为 100 的批处理示例：

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int[][] batchUpdate(final Collection<Actor> actors) {
        int[][] updateCounts = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                actors,
                100,
                new ParameterizedPreparedStatementSetter<Actor>() {
                    public void setValues(PreparedStatement ps, Actor argument) throws SQLException {
                        ps.setString(1, argument.getFirstName());
                        ps.setString(2, argument.getLastName());
                        ps.setLong(3, argument.getId().longValue());
                    }
                });
        return updateCounts;
    }

    // ... additional methods
}

此调用的批处理更新方法返回一个 int 二维数组，第一维数组的长度表示执行的批处理数，第二维数组的长度表示该批处理中的更新数。每个批次中的更新数量应该为所有批次提供的批量大小，但最后一个批次可能会减少，具体取决于提供的更新对象总数。每个更新语句的更新计数由 JDBC 驱动程序报告，如果计数不可用，则 JDBC 驱动程序返回 -2。

参考资料：

Spring 3.x 企业应用开发实战
Spring Framework Reference Documentation